package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleTwoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleTwoServiceImpl implements IGetTotleTwoService {
    @Autowired
    private SqlMapper sqlMapper;

    public String getsqlDepartment(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getSessionAttr(" abbreviation");
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客首次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='新客首次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客多次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客多次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='新客多次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='老客' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='老客' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual "+
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlDepartments(SearchWholeHospitalInfo searchInfo, String classification, String condition) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getSessionAttr(" abbreviation");
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客首次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='新客首次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客多次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='新客多次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='新客多次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='老客' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status='老客' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual "+
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 "+condition+" and b.customer_status in ('新客首次','新客多次','老客') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual ";
        return sql;
    }

    public String getTotleSqlDepartment(SearchWholeHospitalInfo searchInfo) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getSessionAttr(" abbreviation");
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='新客首次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 and b.customer_status='新客首次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='新客多次' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='新客多次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1  and b.customer_status='新客多次' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1  and b.customer_status in ('新客首次','新客多次') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='老客' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 and b.customer_status='老客' and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual "+
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.id from "+tableName2+" b where 1=1 and b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='"+searchInfo.getEndTime()+"' group by b.reservations_id) t) t) AS volume," +
                "(select sum(t.section_performance) from (select a.section_performance from "+tableName3+" a left join "+tableName2+" b on " +
                " a.customer_id=b.id where 1=1 and b.customer_status in ('新客首次','新客多次','老客') and a.recharge_time>='"+searchInfo.getStartTime()+"' and a.recharge_time<='"+searchInfo.getEndTime()+"' " +
                " group by a.id) t) AS departmentPerformance from dual ";
        return sql;
    }
}